﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;

namespace ZG_member
{
    public partial class ExpireQuery : Form
    {
        public ExpireQuery()
        {
            InitializeComponent();
            this.Opacity = 0;
            skinEngine1.SkinFile = Application.StartupPath + ConfigurationManager.AppSettings["Skin"];
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            this.Left += 2;
            this.Opacity += 0.1;
            if (Opacity == 1)
            {
                this.timer1.Stop();

            }
        }

        private void timer2_Tick(object sender, EventArgs e)
        {
            this.Top = this.Top + 10;
            this.Opacity = this.Opacity - 0.06;
            if (Opacity <= 0)
            {
                this.timer2.Stop();
                this.Close();
            }
        }

        private void ExpireQuery_Load(object sender, EventArgs e)
        {
            this.timer1.Start();
            this.comboBox1.SelectedIndex = 0;
            #region 创建对象
            ZG_member.BLL.memberInfo member = new ZG_member.BLL.memberInfo();
            ZG_member.Model.memberInfo memerMode=new ZG_member.Model.memberInfo();
            #endregion
            StringBuilder sb = new StringBuilder();
            sb.Append("select m.memberId,m.memberName,m.sex,m.birthDay,m.createDate,m.expiresDate,w.date ,l.StartDate,l.EndDate ");
            sb.Append(" from memberInfo as m left join ");
            sb.Append("(");
            sb.Append("	select memberCardId,max(date)as date from wasteBook where cardPayMoney>0 or cashPayMoney>0 group by (memberCardId) ");
            sb.Append(" )");
            sb.Append("  as w  on m.memberId=w.memberCardId left join  dbo.Leave as l on m.memberId=l.memberId");
            DataTable dt= member.GetListSql(sb.ToString()).Tables[0];
            this.dataGridView1.DataSource = dt;
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                this.dataGridView1.Rows[i].Cells["SexString"].Value = this.dataGridView1.Rows[i].Cells["Sex"].Value.ToString()=="True"?"男":"女";
            }
        }
        int flag = 0;
        private void ExpireQuery_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (flag == 0)
            {
                e.Cancel = true;
                flag = 1;
                this.timer2.Start();
            }
        }

        private void comboBox1_TextChanged(object sender, EventArgs e)
        {
            #region 创建对象
            ZG_member.BLL.Speak speak = new ZG_member.BLL.Speak();
            ZG_member.Model.Speak speakMode = new ZG_member.Model.Speak();
            speakMode=speak.GetModel(comboBox1.Text);
            #endregion
            textBox1.Text = speakMode.Contents;
            if (comboBox1.Text == "长期未练习")
            {
                this.label1.Visible = false;
                this.dateTimePicker1.Visible = false;
                this.dateTimePicker2.Visible = false;
                this.label2.Visible = false;
                this.numericUpDown1.Visible = true;
                this.label5.Visible = true;
            }
            else
            {
                this.label1.Visible = true;
                this.dateTimePicker1.Visible = true;
                this.dateTimePicker2.Visible = true;
                this.label2.Visible = true;
                this.numericUpDown1.Visible = false;
                this.label5.Visible = false;
            }
        }

        private void dataGridView1_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                this.dataGridView1.Rows[i].Cells["SexString"].Value = this.dataGridView1.Rows[i].Cells["Sex"].Value.ToString() == "True" ? "男" : "女";
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select * from ");
            sb.Append(" ( ");
            sb.Append(" select m.memberId,m.memberName,m.sex,m.birthDay,m.createDate,m.expiresDate,w.date ,l.StartDate,l.EndDate ");
            sb.Append(" from memberInfo as m left join  ");
            sb.Append(" ( ");
            sb.Append(" select memberCardId,max(date)as date from wasteBook where cardPayMoney>0 or cashPayMoney>0 group by (memberCardId) ");
            sb.Append(" ) ");
            sb.Append(" as w  on m.memberId=w.memberCardId left join  dbo.Leave as l on m.memberId=l.memberId ");
            sb.Append(" )as tb ");
            

            ZG_member.BLL.memberInfo member = new ZG_member.BLL.memberInfo();

            if (comboBox1.Text == "生日")
            {
                sb.Append(" where DateDiff(DAY,GETDATE(),DateAdd(YEAR,DateDiff(YEAR,birthDay,GETDATE()),birthDay)) >=0  ");
                sb.Append(" and birthDay>='"+this.dateTimePicker1.Text+"' and birthDay <='"+this.dateTimePicker2.Text+"' ");
                dataGridView1.DataSource = member.GetListSql(sb.ToString()).Tables[0];
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    this.dataGridView1.Rows[i].Cells["SexString"].Value = this.dataGridView1.Rows[i].Cells["Sex"].Value.ToString() == "True" ? "男" : "女";
                }
            }
            if (comboBox1.Text == "快到期会员")
            {
                sb.Append(" where expiresDate>='" + this.dateTimePicker1.Text + "' and expiresDate <='" + this.dateTimePicker2.Text + "' ");
                dataGridView1.DataSource = member.GetListSql(sb.ToString()).Tables[0];
            }
            if (comboBox1.Text == "长期未练习")
            {
                sb.Append(" where DateDiff(DAY,date,GETDATE()) >="+this.numericUpDown1.Value.ToString()+"  ");
                dataGridView1.DataSource = member.GetListSql(sb.ToString()).Tables[0];
            }
            if (comboBox1.Text == "停卡到期")
            {
                sb.Append(" where EndDate<=GETDATE() ");
                dataGridView1.DataSource = member.GetListSql(sb.ToString()).Tables[0];
            }
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                this.dataGridView1.Rows[i].Cells["SexString"].Value = this.dataGridView1.Rows[i].Cells["Sex"].Value.ToString() == "True" ? "男" : "女";
            }

        }

     

     
    }
}
